import json
import pymysql


with open('player.json') as f:
    contents = json.loads(f.read())

conn = pymysql.connect(host="localhost", user="root",
                       password="root", db="nba", port=3306, charset='utf8')

with conn.cursor() as cursor:
    sql = "SELECT displayNameEn FROM player_info"
    cursor.execute(sql)
    result = cursor.fetchall()
    for res in result:
        for r in res:
            for content in contents:
                # 1.选取球员基本信息中的个人信息
                playerProfile = content['playerProfile']
                playerData = [playerProfile.get('displayName'), playerProfile.get('country'),
                              playerProfile.get('jerseyNo'), playerProfile.get('position'),
                              playerProfile.get('draftYear'),
                              playerProfile.get('experience'), playerProfile.get('height'), playerProfile.get('weight'),
                              playerProfile.get('playerId')]
                # 2.选取球员基本信息中的队伍信息

                playerteam = content['teamProfile']
                playerteamData = [playerteam.get('code'), playerteam.get('name')]
                # 3.球员信息和球队信息合并为一个列表
                total = playerData + playerteamData
                #print(playerProfile.get('displayNameEn'))
                if r == playerProfile.get('displayNameEn'):
                    print(total)
                    sql2 = "INSERT INTO player_info_copy(displayName,country,jerseyNo,position,draftYear,experience,height,weight,teamCode,playerid,teamName) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                    effect = cursor.execute(sql2, total)
                    conn.commit()
                else:
                    print("失败")
conn.close()
cursor.close()

